#!/usr/bin/env python3
"""
探索Access数据库的脚本
尝试多种方法获取ERP2.accdb中的表信息
"""

import pyodbc
import os
from pathlib import Path

def explore_access_database():
    """探索Access数据库"""
    print("🔍 开始探索Access数据库...")
    
    # 数据库文件路径
    db_path = Path("ERP2.accdb").absolute()
    print(f"📁 数据库文件: {db_path}")
    
    if not db_path.exists():
        print("❌ 数据库文件不存在")
        return
    
    # 获取可用的Access驱动
    drivers = [driver for driver in pyodbc.drivers() if "Access" in driver]
    print(f"🔧 可用的Access驱动: {drivers}")
    
    if not drivers:
        print("❌ 没有找到Access驱动")
        return
    
    # 使用第一个Access驱动
    driver = drivers[0]
    conn_str = f"Driver={{{driver}}};DBQ={db_path};"
    print(f"🔗 连接字符串: {conn_str}")
    
    try:
        # 连接数据库
        conn = pyodbc.connect(conn_str)
        print("✅ 成功连接到Access数据库")
        
        cursor = conn.cursor()
        
        # 方法1: 尝试查询MSysObjects表
        print("\n📋 方法1: 查询MSysObjects表")
        try:
            cursor.execute("SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0")
            tables = [row[0] for row in cursor.fetchall()]
            print(f"✅ 找到表: {tables}")
        except Exception as e:
            print(f"❌ 方法1失败: {e}")
            tables = []
        
        # 方法2: 尝试查询MSysObjects表（简化）
        if not tables:
            print("\n📋 方法2: 查询MSysObjects表（简化）")
            try:
                cursor.execute("SELECT Name FROM MSysObjects WHERE Type=1")
                tables = [row[0] for row in cursor.fetchall()]
                print(f"✅ 找到表: {tables}")
            except Exception as e:
                print(f"❌ 方法2失败: {e}")
        
        # 方法3: 尝试查询INFORMATION_SCHEMA
        if not tables:
            print("\n📋 方法3: 查询INFORMATION_SCHEMA")
            try:
                cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
                tables = [row[0] for row in cursor.fetchall()]
                print(f"✅ 找到表: {tables}")
            except Exception as e:
                print(f"❌ 方法3失败: {e}")
        
        # 方法4: 尝试查询所有系统表
        if not tables:
            print("\n📋 方法4: 查询所有系统表")
            try:
                cursor.execute("SELECT Name FROM MSysObjects")
                all_objects = [row[0] for row in cursor.fetchall()]
                print(f"所有对象: {all_objects[:20]}...")  # 只显示前20个
                
                # 过滤可能的用户表
                user_tables = [obj for obj in all_objects if not obj.startswith('MSys') and not obj.startswith('~')]
                print(f"可能的用户表: {user_tables}")
                tables = user_tables
            except Exception as e:
                print(f"❌ 方法4失败: {e}")
        
        # 方法5: 尝试常见的表名
        if not tables:
            print("\n📋 方法5: 尝试常见表名")
            common_tables = [
                'Customers', 'Orders', 'Products', 'Employees', 'Suppliers', 'Categories',
                '客户', '订单', '产品', '员工', '供应商', '类别',
                'User', 'Users', 'Table1', 'Data', 'Main', 'Master',
                '客户信息', '订单信息', '产品信息', '员工信息'
            ]
            
            found_tables = []
            for table_name in common_tables:
                try:
                    cursor.execute(f"SELECT TOP 1 * FROM [{table_name}]")
                    found_tables.append(table_name)
                    print(f"✅ 找到表: {table_name}")
                except:
                    pass
            
            if found_tables:
                tables = found_tables
                print(f"✅ 通过常见表名找到: {tables}")
            else:
                print("❌ 没有找到常见表名")
        
        # 如果找到了表，显示详细信息
        if tables:
            print(f"\n🎉 成功找到 {len(tables)} 个表:")
            for i, table in enumerate(tables, 1):
                print(f"  {i}. {table}")
                
                # 尝试获取表结构
                try:
                    cursor.execute(f"SELECT TOP 1 * FROM [{table}]")
                    columns = [desc[0] for desc in cursor.description]
                    print(f"     字段: {', '.join(columns[:5])}{'...' if len(columns) > 5 else ''}")
                except Exception as e:
                    print(f"     无法获取字段信息: {e}")
        else:
            print("\n❌ 没有找到任何表")
            
            # 尝试获取数据库的基本信息
            print("\n📊 尝试获取数据库基本信息:")
            try:
                cursor.execute("SELECT @@VERSION")
                version = cursor.fetchone()
                print(f"数据库版本: {version}")
            except:
                print("无法获取版本信息")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"❌ 连接数据库失败: {e}")

if __name__ == "__main__":
    explore_access_database()
